package com.google.code.netbuilder.dal.providersimpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.google.code.netbuilder.dal.entities.Equipment;
import com.google.code.netbuilder.dal.providers.IEquipmentProvider;
import com.google.code.netbuilder.dal.services.JDBCServise;

/**
 * @author 
 *
 */
public class EquipmentProviderJDBC implements IEquipmentProvider {

	@Override
	public void save(Equipment entity) throws RuntimeException{
		String query = "INSERT INTO Equipment(id, name, type, address, isBroken, isReserved, number_of_ports, date)" +
				" VALUES (?,?,?,?,?,?,?,?);";
		Connection con = null;
		PreparedStatement pst = null;
		try
		{
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			int rs=-1;
			pst = con.prepareStatement(query);
			pst.setString(1, entity.getId().toString());
			pst.setString(2, entity.getName());
			pst.setString(3, entity.getType());
			pst.setString(4, entity.getAddress());
			pst.setInt(5, entity.getIsBroken());
			pst.setInt(6, entity.getIsReserved());
			pst.setInt(7, entity.getNumberOfPorts());
			pst.setDate(8, new Date(entity.getDate().getTime()));
			rs = pst.executeUpdate();
			if (rs<=0) throw new SQLException("Query didn't execute");
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public void update(Equipment entity) throws RuntimeException {
		String query = "UPDATE Equipment SET name=?, type=?, address=?, " +
				"isReserved=?, isBroken=?, number_of_ports=?, date=? WHERE id=?;";		
		
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			pst = con.prepareStatement(query);
			pst.setString(1, entity.getName());
			pst.setString(2, entity.getType());
			pst.setString(3, entity.getAddress());
			pst.setInt(4, entity.getIsReserved());
			pst.setInt(5, entity.getIsBroken());		
			pst.setInt(6, entity.getNumberOfPorts());
			pst.setDate(7, new Date(entity.getDate().getTime()));
			pst.setInt(8, entity.getId());			
			int rs = pst.executeUpdate();		
			if (rs <= 0) throw new SQLException("Equipment didn't update");
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public void delete(Equipment entity) throws RuntimeException {
		String query1 = "DELETE FROM equipment_version WHERE equipment_id=?;";
		String query2 = "DELETE FROM equipment_port WHERE id_equipment=?;";
		String query3 = "DELETE FROM equipment WHERE id=?;";
		
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			int id = entity.getId();			
			pst = con.prepareStatement(query1);
			pst.setInt(1, id);		
			pst.executeUpdate();	
			pst = con.prepareStatement(query2);
			pst.setInt(1, id);		
			pst.executeUpdate();
			pst = con.prepareStatement(query3);
			pst.setInt(1, id);		
			pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}		
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public List<Equipment> findAll() throws RuntimeException {		
		return getListEquipment("Select * from Equipment");
	}

	@Override
	public Equipment findByID(Integer id) throws RuntimeException {
		
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Equipment equipment = null;
		try {
			con = JDBCServise.getConnection();
	     
			if (con == null) throw new SQLException("No Data Base Connection");          
			pst = con.prepareStatement("SELECT * FROM equipment where id ="
        								+ id.toString());
			rs = pst.executeQuery();
       
			if (!rs.first()) throw new SQLException("Equipment not found");
			
			equipment = new Equipment();
        
			equipment.setId(rs.getInt("id"));
			equipment.setName(rs.getString("name"));
			equipment.setAddress(rs.getString("address"));
			equipment.setNumberOfPorts(rs.getInt("number_of_ports"));
			equipment.setIsBroken(rs.getInt("isBroken"));
			equipment.setIsReserved(rs.getInt("isreserved"));
			equipment.setDate(rs.getDate("date").getTime());			
			
		} catch (SQLException exc)
		{
			throw new RuntimeException(exc);
		}
        
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
				}
		}
		return equipment;
	}

	@Override
	public List<Equipment> getEquipmentsByAddress(String address) throws RuntimeException {
		return getListEquipment("Select * from Equipment where address='"+
        												address + "'");        
	}

	@Override
	public List<Equipment> getEquipmentsByName(String name) throws RuntimeException {
		return getListEquipment("Select * from Equipment where name='"+
								name + "'");
	}
	
	private List<Equipment> getListEquipment(String query) throws RuntimeException
	{
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<Equipment> equipments = null;
		
		try {
			con = JDBCServise.getConnection();
        
			pst = con.prepareStatement(query);
			rs = pst.executeQuery();
        
			equipments = new ArrayList<Equipment>();
        
			while (rs.next()) {
				Equipment e = new Equipment();
				e.setId(rs.getInt("id"));
				e.setName(rs.getString("name"));
				e.setAddress(rs.getString("address"));
				e.setNumberOfPorts(rs.getInt("number_of_ports"));
				e.setIsBroken(rs.getInt("isbroken"));
				e.setIsReserved(rs.getInt("isreserved"));
				e.setDate(rs.getDate("date").getTime());
				equipments.add(e);
			}
		} catch (SQLException exc)
		{
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
				}
		}	
		
        return equipments;		
	}
}
